<!DOCTYPE html>
<html lang="en">
<head>
    <title id='Description'>Implementing jqxDataTable Server Sorting, Filtering and Paging with JSP and MySQL.</title>
    <link rel="stylesheet" href="../../jqwidgets/styles/jqx.base.css" type="text/css" />
    <script type="text/javascript" src="../../scripts/jquery-1.11.1.min.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxcore.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxdata.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxbuttons.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxscrollbar.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxdatatable.js"></script>
    <script type="text/javascript" src="../../scripts/demos.js"></script>
    <script type="text/javascript">
        $(document).ready(function() {
            // prepare the data
            var source = {
                dataType: "json",
                dataFields: [{
                    name: 'FirstName',
                    type: 'string'
                }, {
                    name: 'LastName',
                    type: 'string'
                }, {
                    name: 'Title',
                    type: 'string'
                }, {
                    name: 'BirthDate',
                    type: 'date'
                }],
                id: 'EmployeeID',
                url: 'jsp/populate-datatable.jsp',
                beforeprocessing: function(data) {
                    if (data != null && data.length > 0) {
                        source.totalRecords = parseInt(data[0].totalRecords);
                    }
                }
            };
            var filterChanged = false;
            var dataAdapter = new $.jqx.dataAdapter(source, {
                formatData: function(data) {
                    if (data.sortdatafield && data.sortorder) {
                        // update the $orderby param of the OData service.
                        // data.sortdatafield - the column's datafield value(ShipCountry, ShipCity, etc.).
                        // data.sortorder - the sort order(asc or desc).
                        data.$orderby = data.sortdatafield + " " + data.sortorder;
                    }
                    if (data.filterslength) {
                        filterChanged = true;
                        var filterParam = "";
                        for (var i = 0; i < data.filterslength; i++) {
                            // filter's value.
                            var filterValue = data["filtervalue" + i];
                            // filter's condition. For the filterMode="simple" it is "CONTAINS".
                            var filterCondition = data["filtercondition" + i];
                            // filter's data field - the filter column's datafield value.
                            var filterDataField = data["filterdatafield" + i];
                            // "and" or "or" depending on the filter expressions. When the filterMode="simple", the value is "or".
                            var filterOperator = data[filterDataField + "operator"];

                            var startIndex = 0;
                            if (filterValue.indexOf('-') == -1) {
                                if (filterCondition == "CONTAINS") {
                                    filterParam += filterDataField + ' LIKE "%' + filterValue + '%"';
                                    //                                        filterParam += "substringof('" + filterValue + "', " + filterDataField + ") eq true";
                                    filterParam += " " + filterOperator + " ";
                                }
                            } else {
                                if (filterDataField == "BirthDate") {
                                    var dateGroups = new Array();
                                    var startIndex = 0;
                                    var item = filterValue.substring(startIndex).indexOf('-');
                                    while (item > -1) {
                                        dateGroups.push(filterValue.substring(startIndex, item + startIndex));
                                        startIndex += item + 1;
                                        item = filterValue.substring(startIndex).indexOf('-');
                                        if (item == -1) {
                                            dateGroups.push(filterValue.substring(startIndex));
                                        }
                                    }
                                    if (dateGroups.length == 3) {
                                        filterParam += "YEAR(BirthDate) = " + parseInt(dateGroups[0]) + " AND MONTH(BirthDate) = " + parseInt(dateGroups[1]) + " AND DAY(BirthDate) = " + parseInt(dateGroups[2]);
                                    }
                                    filterParam += " " + filterOperator + " ";
                                }
                            }
                        }
                        // remove last filter operator.
                        filterParam = filterParam.substring(0, filterParam.length - filterOperator.length - 2);

                        data.$filter = filterParam;
                        source.totalRecords = 0;
                    } else {
                        if (filterChanged) {
                            source.totalRecords = 0;
                            filterChanged = false;
                        }
                    }
                    if (source.totalRecords) {
                        // update the $skip and $top params of the OData service.
                        // data.pagenum - page number starting from 0.
                        // data.pagesize - page size
                        data.$skip = data.pagenum * data.pagesize;
                        data.$top = data.pagesize;
                    }
                    return data;
                },
                downloadComplete: function(data, status, xhr) {
                    if (!source.totalRecords) {
                        source.totalRecords = data.length;
                    }
                },
                loadError: function(xhr, status, error) {
                    throw new Error(error);
                }
            });
            $("#dataTable").jqxDataTable({
                width: 550,
                pageable: true,
                pageSize: 3,
                pagerButtonsCount: 10,
                serverProcessing: true,
                source: dataAdapter,
                altRows: true,
                filterable: true,
                filterMode: 'simple',
                sortable: true,
                columnsResize: true,
                columns: [{
                    text: 'First Name',
                    dataField: 'FirstName',
                    width: 100
                }, {
                    text: 'Last Name',
                    dataField: 'LastName',
                    width: 100
                }, {
                    text: 'Title',
                    dataField: 'Title',
                    width: 180
                }, {
                    text: 'Birth Date',
                    dataField: 'BirthDate',
                    cellsFormat: 'yyyy-MM-dd',
                    align: 'right',
                    cellsAlign: 'right'
                }]
            });
        });
    </script>
</head>
<body class='default'>
    <div id="dataTable"></div>
</body>
</html>